你知道 DBA 工作中都要做的巡检有哪些吗？

作者 | JiekeXu

来源 | JiekeXu之路（ID: JiekeXu_IT）

转载请联系授权 | (微信ID：xxq1426321293)

大家好，我是 JiekeXu,很高兴和大家又见面了，今天分享下 Oracle DBA 工作中都需要做的数据库巡检有哪些？本文首发于微信公众号【JiekeXu之路】，欢迎点击上方蓝字关注我吧！

最近有很多小伙伴们都在陆陆续续的上班了，结束了远程办公时刻，不能浑水摸鱼了，那么我也不例外，下周开始现场轮班了，首先要做的就是检查数据库的信息，填写一张关于数据库信息的巡检表，下面我们就一起来捋一捋，Oracle DBA 工作中都需要做的数据库巡检有哪些？

一、查看有几个实例：

ps -ef|grep smon

oracle    9069 29581  0 11:02 pts/0    00:00:00 grep smon
oracle   27814     1  0 Mar30 ?        00:01:41 ora_smon_orcl2
root     28394     1  1  2019 ?        5-01:48:28 /app/product/11.2.0/grid/bin/osysmond.bin
grid     28870     1  0  2019 ?        00:13:38 asm_smon_+ASM2
oracle   32266     1  0  2019 ?        00:15:40 ora_smon_PROD3

二、查看数据库状态

su - grid 
cs-testr2:/home/grid$crsctl status res -t 
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ARCH.dg
               ONLINE  ONLINE       cs-testr1                               
               ONLINE  ONLINE       cs-testr2                               
ora.DATA.dg
               ONLINE  ONLINE       cs-testr1                               
               ONLINE  ONLINE       cs-testr2                               
ora.LISTENER.lsnr
               ONLINE  ONLINE       cs-testr1                               
               ONLINE  ONLINE       cs-testr2                               
ora.OCR.dg
               ONLINE  ONLINE       cs-testr1                               
               ONLINE  ONLINE       cs-testr2                               
ora.asm
               ONLINE  ONLINE       cs-testr1           Started             
               ONLINE  ONLINE       cs-testr2           Started             
ora.gsd
               OFFLINE OFFLINE      cs-testr1                               
               OFFLINE OFFLINE      cs-testr2                               
ora.net1.network
               ONLINE  ONLINE       cs-testr1                               
               ONLINE  ONLINE       cs-testr2                               
ora.ons
               ONLINE  ONLINE       cs-testr1                               
               ONLINE  ONLINE       cs-testr2                               
ora.registry.acfs
               ONLINE  ONLINE       cs-testr1                               
               ONLINE  ONLINE       cs-testr2                               
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       cs-testr1                               
ora.cs-testr1.vip
      1        ONLINE  ONLINE       cs-testr1                               
ora.cs-testr2.vip
      1        ONLINE  ONLINE       cs-testr2                               
ora.cvu
      1        ONLINE  ONLINE       cs-testr1                               
ora.oc4j
      1        ONLINE  ONLINE       cs-testr1                               
ora.orcl.db
      1        ONLINE  ONLINE       cs-testr1           Open                
      2        ONLINE  ONLINE       cs-testr2           Open                
ora.scan1.vip
      1        ONLINE  ONLINE       cs-testr1

#检查监听状态
cs-testr2:/home/grid$ lsnrctl status 

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 10-APR-2020 18:13:27

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                23-JUL-2019 10:26:56
Uptime                    262 days 7 hr. 46 min. 31 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /app/product/11.2.0/grid/network/admin/listener.ora
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1XX.XX.XX)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1XX.XX.XX)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "ORCLXDB.us.exampl.com" has 1 instance(s).
  Instance "PROD3", status READY, has 1 handler(s) for this service...
Service "PROD3.us.exampl.com" has 1 instance(s).
  Instance "PROD3", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl2", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl2", status READY, has 1 handler(s) for this service...
The command completed successfully

三、检查用户连接

cs-testr2:/home/grid$ps -ef | grep LOCAL=NO | grep -v grep | awk '{print $2}' | wc -l  
45

四、检查后台日志

#数据库alert
tail -500f  $ORACLE_BASE/diag/rdbms/orcl/orcl2/trace/alert_orcl2.log |more
#ASM 日志
tail -500f  $ORACLE_BASE/diag/asm/+asm/+ASM2/trace/alert_+ASM2.log
#集群日志
tail -200f  $ORACLE_HOME/log/cs-testr2/alertcs-testr2.log
#监听日志
tail -200f $ORACLE_HOME/network/log/listener.log

五、检查 sudo 配置;

sudo -l
Matching Defaults entries for oracle on this host:
    runaspw


User oracle may run the following commands on this host:
    (root) NOPASSWD: /bin/kill, /usr/bin/printenv, /app/oraInventory/orainstRoot.sh, /app/product/11.2.0/db/root.sh, /app/product/11.2.0/db/OPatch/opatch

-------------------------------------------------------------------

以上完成了基本巡检，下面需要填写一些固定信息

-------------------------------------------------------------------

六、完善表格中的所有信息列（更新+补充）:

-------------以 SUSE 系统为例：------------

#查看CPU信息（型号）

cat /proc/cpuinfo | grep name | cut -f2 -d: | uniq -c
4  AMD Opteron(TM) Processor 6234

#检查操作系统版本

#查看物理 CPU 个数：

cat /proc/cpuinfo| grep "physical id"| sort| uniq| wc -l
2

# 查看逻辑 CPU 的个数

cat /proc/cpuinfo| grep "processor"| wc -l
4

#查看内存：

free -g
             total       used       free     shared    buffers     cached
Mem:             7          7          0          0          0          5
-/+ buffers/cache:          1          5
Swap:            7          0          7

--------------------AIX 系统----------------

#主机型号：
uname -um
00F8F7964C00 IBM,0206F796T

#主机序列号：
uname -uM
IBM,9179-MHD IBM,0206F796T

#查看CPU信息（型号）

prtconf|more （查看Processor Type 行）
System Model: IBM,9179-MHD
Machine Serial Number: 06F796T
Processor Type: PowerPC_POWER7
Processor Implementation Mode: POWER 7
Processor Version: PV_7_Compat
Number Of Processors: 64
Processor Clock Speed: 3724 MHz
CPU Type: 64-bit
Kernel Type: 64-bit
LPAR Info: 3 KHYXDB1
Memory Size: 311296 MB
Good Memory Size: 311296 MB
Platform Firmware level: AM760_068
Firmware Version: IBM,AM760_068
Console Login: enable
Auto Restart: true
Full Core: false


--当然也可以过滤下：
prtconf|grep 'Processor Type'
Processor Type: PowerPC_POWER7

#查看物理CPU个数：

prtconf|grep Processors
Number Of Processors: 64

#逻辑CPU：

pmcycles -m | wc -l
256

#查看内存：

prtconf|grep Memory
Memory Size: 311296 MB
Good Memory Size: 311296 MB
+ mem0                                                                             Memory

--------------HPUX:命令输出信息省略-------------

#查看主机型号 
machinfo
(Model)
#主机序列号：
machinfo
（查看Platform info:下的Machine serial number）
#查看CPU信息（型号）
machinfo

#查看物理CPU个数（HP只有物理C）：
machinfo
（查看CPU info下的24 logical processors，24即为cpu个数）
#内存：
machinfo

#查看主机名：
hostname

#查看 ip 地址映射：
cat /etc/hosts
#查看端口号：
lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 10-APR-2020 14:23:20


Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                23-JUL-2019 10:26:56
Uptime                    262 days 3 hr. 56 min. 23 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /app/product/11.2.0/grid/network/admin/listener.ora
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1XX.XX.XX)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1XX.XX.XX)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "ORCLXDB.us.exampl.com" has 1 instance(s).
  Instance "PROD3", status READY, has 1 handler(s) for this service...
Service "PROD3.us.exampl.com" has 1 instance(s).
  Instance "PROD3", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl2", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl2", status READY, has 1 handler(s) for this service...

-----------------数据库实例级别检查------------------

su - oracle
sqlplus / as sysdba

#查看实例名，数据库名：

show parameter name
SYS@orcl2> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string      orcl
db_unique_name                       string      orcl
global_names                         boolean     FALSE
instance_name                        string      orcl2
lock_name_space                      string
log_file_name_convert                string
processor_group_name                 string
service_names                        string      orcl

#查看字符集：

select userenv('language') from dual;

SYS@orcl2> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8

#查看连接数：

show parameter processes

SYS@orcl2> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     1
db_writer_processes                  integer     1
gcs_server_processes                 integer     2
global_txn_processes                 integer     1
job_queue_processes                  integer     15
log_archive_max_processes            integer     4
processes                            integer     2000

#查看在线日志组的大小：

set linesize 250
COLUMN groupno                  FORMAT a6                 HEADING 'Group'   
COLUMN thread                     FORMAT a6                 HEADING 'Thread'   
COLUMN member                  FORMAT a50                HEADING 'Member'         
COLUMN redo_file_type         FORMAT a10                HEADING 'Redo Type'      
COLUMN group_status           FORMAT a12                HEADING 'Group Status'
COLUMN member_status       FORMAT a15                HEADING 'Member Status'     
COLUMN bytes                       FORMAT 999,999            HEADING 'Size(M)'        
COLUMN archived                  FORMAT a10                HEADING 'Archived'
BREAK ON groupno
SELECT to_char(f.group#)  groupno, to_char(l.thread#) thread, f.member member, f.type  redo_file_type, l.status group_status, f.status member_status, l.bytes/1024/1024 bytes, l.archived archived FROM v$logfile f, v$log l WHERE f.group# = l.group# ORDER BY f.group#, f.member;

Group  Thread Member                                             Redo Type  Group Status Member Status    Size(M) Archived
------ ------ -------------------------------------------------- ---------- ------------ --------------- -------- ----------
1      1      +DATA/orcl/onlinelog/group_1.261.1014371369        ONLINE     INACTIVE                          512 YES
2      1      +DATA/orcl/onlinelog/group_2.262.1014371373        ONLINE     CURRENT                           512 NO
3      2      +DATA/orcl/onlinelog/group_3.265.1014371591        ONLINE     CURRENT                           512 NO
4      2      +DATA/orcl/onlinelog/group_4.266.1014371593        ONLINE     INACTIVE                          512 YES

【需要重点记录归档是否开启，是否有备份】

SYS@orcl2> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +ARCH
Oldest online log sequence     66
Next log sequence to archive   67
Current log sequence           67 

#归档目录大小根据情况查：

#若ASM：
select group_number,name,total_mb/1024 total_gb,free_mb/1024 free_gb,TYPE from v$asm_diskgroup;
GROUP_NUMBER NAME                             TOTAL_GB    FREE_GB TYPE
------------ ------------------------------ ---------- ---------- ------
           1 ARCH                                   50 4.63964844 EXTERN
           2 DATA                                  100         92 EXTERN
           3 OCR                                     3 2.09570313 NORMAL
           
#若文件系统：
df -h/df -g/bdf
SYS@orcl2> ! df -h 
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda3       189G   60G  119G  34% /
udev            3.9G  132K  3.9G   1% /dev
tmpfs           3.9G  492M  3.4G  13% /dev/shm
/dev/sda1      1011M   62M  899M   7% /boot

#检查备份情况

【ADG 备库需要部署删除归档的脚本】：

①查询是否有备份：
col START_TIME for a30
col END_TIME for a30
col status for a10
select SESSION_KEY,INPUT_TYPE,STATUS,to_char(START_TIME, 'yyyy-mm-dd hh24:mi') START_TIME,to_char(END_TIME, 'yyyy-mm-dd hh24:mi') 
END_TIME,ELAPSED_SECONDS / 3600 from v$rman_backup_job_details 
where to_char(START_TIME, 'yyyy-mm-dd hh24:mi') > to_char(sysdate-12,'yyyy-mm-dd hh24:mi') order by SESSION_KEY;
SESSION_KEY INPUT_TYPE    STATUS     START_TIME                     END_TIME                       ELAPSED_SECONDS/3600
----------- ------------- ---------- ------------------------------ ------------------------------ --------------------
      63833 ARCHIVELOG    COMPLETED  2020-03-29 19:13               2020-03-29 19:31                         .289444444
      63841 DB INCR       COMPLETED  2020-03-30 00:20               2020-03-30 03:01                         2.67833333
      63843 ARCHIVELOG    COMPLETED  2020-03-30 00:55               2020-03-30 01:25                         .506388889
      63851 ARCHIVELOG    COMPLETED  2020-03-30 07:30               2020-03-30 08:06                         .599444444
      63859 ARCHIVELOG    COMPLETED  2020-03-30 13:55               2020-03-30 14:08                         .220555556
      63867 ARCHIVELOG    COMPLETED  2020-03-30 19:12               2020-03-30 19:26                         .238888889
      63875 DB INCR       COMPLETED  2020-03-31 00:01               2020-03-31 02:42                         2.68805556
      63877 ARCHIVELOG    COMPLETED  2020-03-31 00:28               2020-03-31 01:06                         .636666667
      63885 ARCHIVELOG    COMPLETED  2020-03-31 06:48               2020-03-31 07:26                               .635
      63893 ARCHIVELOG    COMPLETED  2020-03-31 12:13               2020-03-31 12:31                         .302222222
      63901 ARCHIVELOG    COMPLETED  2020-03-31 19:30               2020-03-31 19:44                         .234166667
      63909 DB INCR       COMPLETED  2020-04-01 00:26               2020-04-01 02:38                         2.19027778
      63911 ARCHIVELOG    COMPLETED  2020-04-01 00:31               2020-04-01 00:39                              .1475
      63919 ARCHIVELOG    COMPLETED  2020-04-01 06:29               2020-04-01 07:08                         .648611111
      63927 ARCHIVELOG    COMPLETED  2020-04-01 12:36               2020-04-01 12:48                              .2025

SQL> col END_TIME for a20 
SQL> select input_bytes_display,start_time,end_time,status,input_type,ELAPSED_SECONDS from V$RMAN_BACKUP_JOB_DETAILS  where input_type ='DB INCR' order by start_time;

INPUT_BYTES_DISPLAY  START_TIME           END_TIME             STATUS     INPUT_TYPE    ELAPSED_SECONDS
-------------------- -------------------- -------------------- ---------- ------------- ---------------
    1.61T            04-FEB-20            05-FEB-20            COMPLETED  DB INCR                  5572
    2.10T            05-FEB-20            06-FEB-20            COMPLETED  DB INCR                  6619
    2.30T            06-FEB-20            07-FEB-20            COMPLETED  DB INCR                  9305
    2.18T            08-FEB-20            08-FEB-20            COMPLETED  DB INCR                  7596
    2.39T            09-FEB-20            09-FEB-20            COMPLETED  DB INCR                 11599
    2.26T            09-FEB-20            10-FEB-20            COMPLETED  DB INCR                  7862
   28.11T            10-FEB-20            11-FEB-20            COMPLETED  DB INCR                 89220
    1.63T            12-FEB-20            12-FEB-20            COMPLETED  DB INCR                  7650
    2.53T            12-FEB-20            13-FEB-20            COMPLETED  DB INCR                  8945
    2.54T            14-FEB-20            14-FEB-20            COMPLETED  DB INCR                  7906

#若数据库有备库，查看备库是否正常，若异常及时恢复；

①查看有没有备库：

SQL> show parameter log_archive_dest_2


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=xxyx3  VALID_FOR=(ON
                                                 LINE_LOGFILES,PRIMARY_ROLE) DB
                                                 _UNIQUE_NAME=xxyx3

②查看备库同步情况，备库查询：
set linesize 150;
set pagesize 20;
column name format a13;
column value format a20;
column unit format a30;
column TIME_COMPUTED format a30;
select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag');

#查看数据库版本：

set line 150   
col ACTION_TIME for a30    
col ACTION for a8    
col NAMESPACE for a8    
col VERSION for a10    
col BUNDLE_SERIES for a5    
col COMMENTS for a20    
select * from dba_registry_history;
ACTION_TIME                    ACTION   NAMESPAC VERSION            ID BUNDL COMMENTS
------------------------------ -------- -------- ---------- ---------- ----- --------------------
17-JUL-14 05.30.00.525281 PM   APPLY    SERVER   11.2.0.3            9 PSU   PSU 11.2.0.3.9

#查看数据库补丁版本：

PS：这项忘记了，截个图吧。

su -grid
$ORACLE_HOME/OPatch/opatch lsinventory

#安装日期：

select NAME,CREATED from gv$database;
SQL> select NAME,CREATED from gv$database;

NAME          CREATED
------------- ------------------
XXYX        17-JUL-14
XXYX        17-JUL-14

#表空间使用情况（超过 80% 需扩容，磁盘使用超过 85% 需要加盘）

SELECT a.tablespace_name,round(total/1024/1024/1024) "Total g",
round(free/1024/1024/1024) "Free g",ROUND((total-free)/total,4)*100 "USED%"
FROM (SELECT tablespace_name,SUM(bytes) free FROM
DBA_FREE_SPACE
GROUP BY tablespace_name ) a,
(SELECT tablespace_name,SUM(bytes) total FROM DBA_DATA_FILES
GROUP BY tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name
ORDER BY 4;
TABLESPACE_NAME                   Total g     Free g      USED%
------------------------------ ---------- ---------- ----------
UNDOTBS2                                2          2        .38
USERS                                   0          0       2.63
UNDOTBS1                                0          0      18.42
SYSAUX                                  3          1      56.53
SYSTEM                                  1          0      99.45

#磁盘使用情况：

select group_number,name,total_mb/1024 total_gb,free_mb/1024 free_gb,TYPE from v$asm_diskgroup;
GROUP_NUMBER NAME                             TOTAL_GB    FREE_GB TYPE
------------ ------------------------------ ---------- ---------- ------
           1 ARCH                                   50 4.63964844 EXTERN
           2 DATA                                  100         92 EXTERN
           3 OCR                                     3 2.09570313 NORMAL

#数据库表空间大小：

SELECT round(SUM(bytes / (1024*1024*1024)), 0) "ts_size g" 
FROM dba_tablespaces t, dba_data_files d 
WHERE t.tablespace_name = d.tablespace_name;


SYS@orcl2> SELECT round(SUM(bytes / (1024*1024*1024)), 0) "ts_size g" 
  2  FROM dba_tablespaces t, dba_data_files d 
  3  WHERE t.tablespace_name = d.tablespace_name;

 ts_size g
----------
         6

#数据库表空间已使用大小（GB）(数据量)：

select sum(bytes)/1024/1024 mb from dba_segments;
SYS@orcl2> select sum(bytes)/1024/1024 mb from dba_segments;
        MB
----------
  2712.375

好了，到这里就算完事了，可以休息了。写作不易，此文如果对你有帮助，请支持点“在看”与转发，你的支持便是我最大的动力，让我们一起努力做更好的自己！

点亮在看，你最好看！